﻿Imports System.Data
Imports System.Data.SqlClient

Public Class DatabaseClass
    Public dataConn As System.Data.SqlClient.SqlConnection
    Public dataComm As System.Data.SqlClient.SqlCommand
    Public dataRead As System.Data.SqlClient.SqlDataReader
    Public dataTran As System.Data.SqlClient.SqlTransaction
    Public dataAdap As System.Data.SqlClient.SqlDataAdapter

    Public strDataString As String = ""

    'Khởi tạo Object
    Public Sub New(Optional ByVal ConnectString As String = "")
        MyBase.new()
        If ConnectString <> "" Then strDataString = ConnectString
    End Sub

    'Mở kết nối dữ liệu
    Public Sub ConnectData()
        If dataConn Is Nothing Then dataConn = New System.Data.SqlClient.SqlConnection
        If dataConn.State = System.Data.ConnectionState.Closed Then
            dataConn.ConnectionString = vbNullString
            dataConn.ConnectionString = strDataString
            dataConn.Open()
        End If

        If dataComm Is Nothing Then dataComm = New System.Data.SqlClient.SqlCommand
        If dataAdap Is Nothing Then dataAdap = New System.Data.SqlClient.SqlDataAdapter
        dataComm.Connection = dataConn
        dataComm.CommandTimeout = 60
        dataComm.CommandType = System.Data.CommandType.Text

    End Sub

    'Đóng kết nối dữ liệu
    Public Sub CloseData()
        If Not dataRead Is Nothing Then dataRead.Close()
        If Not dataConn Is Nothing Then If dataConn.State = System.Data.ConnectionState.Open Then dataConn.Close()
        dataRead = Nothing
        dataConn = Nothing
    End Sub

    'Chạy câu lệnh ExecuteNonQuery (không trả về dữ liệu)
    Public Sub ExeNonQuery(ByVal strQuery As String)
        If dataConn Is Nothing Then Call ConnectData()
        If Not dataRead Is Nothing Then dataRead.Close()
        dataComm.CommandText = strQuery
        dataComm.ExecuteNonQuery()
    End Sub

    'Chạy câu lệnh ExecuteReader (có hoặc không trả về dữ liệu - lưu ý đóng dữ liệu)
    Public Sub ExeReader(ByVal strQuery As String)
        If dataConn Is Nothing Then Call ConnectData()
        If Not dataRead Is Nothing Then dataRead.Close()
        dataComm.CommandText = strQuery
        dataRead = dataComm.ExecuteReader()
    End Sub

    Public Function ExeDataset(ByVal strQuery As String) As DataSet
        If dataConn Is Nothing Then Call ConnectData()
        If Not dataRead Is Nothing Then dataRead.Close()
        dataComm.CommandText = strQuery
        dataAdap.SelectCommand = dataComm
        Dim ds As New System.Data.DataSet
        dataAdap.Fill(ds)
        Return ds
    End Function

    Public Function ExeDataTable(ByVal strQuery As String) As DataTable
        If dataConn Is Nothing Then Call ConnectData()
        If Not dataRead Is Nothing Then dataRead.Close()
        dataComm.CommandText = strQuery
        dataAdap.SelectCommand = dataComm
        Dim ds As New System.Data.DataSet
        dataAdap.Fill(ds)
        Dim dt As New System.Data.DataTable
        dt = ds.Tables(0)
        Return dt
    End Function

    'Bắt đầu Transaction
    Public Sub BeginTrans()
        If dataConn Is Nothing Then Call ConnectData()
        If Not dataRead Is Nothing Then dataRead.Close()
        dataTran = dataConn.BeginTransaction()
        dataComm.Transaction = dataTran
    End Sub

    'Kết thúc Transaction
    Public Sub CommitTrans()
        If Not dataRead Is Nothing Then dataRead.Close()
        dataTran.Commit()
    End Sub

    'Quay lại các lệnh cho dữ liệu
    Public Sub RollBackTrans()
        If Not dataRead Is Nothing Then dataRead.Close()
        dataTran.Rollback()
    End Sub
End Class

